﻿/*
************************************************************
CreateDate  *  Author        *  Content
2008-07-04  *  Donne.Yang  *  Query data and execute data
************************************************************
NO   *  UpdateDate   *  Author        *  Content

*************************************************************
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;

namespace Stc.Dbo
{
    public class OleDBOpration
    {
        private string strConn;//DB

        public OleDBOpration()
        {
            if (Configuration.OleConn == null)
            {
                this.strConn = ConfigurationSettings.AppSettings["OleConn"];
                Configuration.OleConn = ConfigurationSettings.AppSettings["OleConn"];
            }
            else
            {
                this.strConn = Configuration.OleConn;
            }
        }

        public OleDBOpration(string strConn)
        {
            this.strConn = strConn;
        }

        /// <summary>
        /// Connect Oracle DB 
        /// </summary>
        /// <returns>Oracle DB Connection</returns>
        private OleDbConnection GetConnection()
        {
            OleDbConnection oleDbConnection = null;
            try
            {
                oleDbConnection = new OleDbConnection(strConn);
                oleDbConnection.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return oleDbConnection;
        }

        /// <summary>
        /// Close Oracle DB Connection
        /// </summary>
        /// <param name="oleDbConnection">OleDbConnection</param>
        private void CloseDB(OleDbConnection oleDbConnection)
        {
            if (oleDbConnection.State != ConnectionState.Closed)
            {
                try
                {
                    oleDbConnection.Close();
                    oleDbConnection.Dispose();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// Query data from oracle DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>data with dataset format</returns>
        public DataSet GetResult(string strSql)
        {
            OleDbConnection oleDbConnection = GetConnection();

            DataSet dsData = new DataSet();

            try
            {
                OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter();

                oleDbDataAdapter.SelectCommand = new OleDbCommand(strSql, oleDbConnection);

                oleDbDataAdapter.Fill(dsData);
                oleDbDataAdapter.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(oleDbConnection);
            }

            return dsData;
        }

        /// <summary>
        /// Query data from oracle DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>data with datareader format</returns>
        public OleDbDataReader GetResultRead(string strSql)
        {
            OleDbConnection oleDbConnection = GetConnection();

            OleDbDataReader oleDbDataReader = null;

            try
            {
                OleDbCommand oleDbCommand = new OleDbCommand(strSql, oleDbConnection);

                oleDbDataReader = oleDbCommand.ExecuteReader();
                oleDbCommand.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(oleDbConnection);
            }

            return oleDbDataReader;
        }

        /// <summary>
        /// update data of OLEDB DB
        /// </summary>
        /// <param name="strSql">sql sentence</param>
        /// <returns>execute state with ture or false</returns>
        public bool UpdateData(string strSql)
        {
            bool blnFlag = false;

            OleDbConnection oleDbConnection = GetConnection();

            try
            {
                OleDbCommand oleDbCommand = new OleDbCommand(strSql, oleDbConnection);

                if (oleDbCommand.ExecuteNonQuery() > 0)
                {
                    blnFlag = true;
                }
                oleDbCommand.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(oleDbConnection);
            }

            return blnFlag;
        }
        
        /// <summary>
        /// update data of OLEDB DB with Paramters
        /// </summary>
        /// <param name="strSql">Sql string</param>
        /// <param name="Parameters">Parameters</param>
        /// <returns>bool</returns>
        public bool UpdateData(string strSql, OleDbParameter[] Parameters)
        {
            bool blnFlag = false;
            int num = 0;

            OleDbConnection oleDbConnection = GetConnection();

            try
            {
                OleDbCommand oleDbCommand = oleDbConnection.CreateCommand();
                oleDbCommand.CommandText = strSql;
                oleDbCommand.CommandType = CommandType.Text;
                oleDbCommand.Parameters.AddRange(Parameters);

                num = oleDbCommand.ExecuteNonQuery();
                oleDbCommand.Parameters.Clear();
                oleDbCommand.Dispose();

                if (num > 0)
                {
                    blnFlag = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB(oleDbConnection);
            }

            return blnFlag;
        }
        /// <summary>
        /// Execute multiple sql sentences of update orcale DB
        /// </summary>
        /// <param name="arrSqls">sql sentences</param>
        /// <returns>execute state with ture or false</returns>
        public bool ExecuteTransaction(ArrayList arrSqls)
        {
            bool blnFlag = false;

            OleDbConnection oleDbConnection = GetConnection();

            OleDbTransaction oleDbTransaction = oleDbConnection.BeginTransaction();
            OleDbCommand oleDbCommand = oleDbConnection.CreateCommand();
            oleDbCommand.Transaction = oleDbTransaction;

            try
            {
                for (int i = 0; i < arrSqls.Count; i++)
                {
                    oleDbCommand.CommandText = (string)arrSqls[i];
                    oleDbCommand.ExecuteNonQuery();
                }

                oleDbTransaction.Commit();

                blnFlag = true;
            }
            catch (Exception ex)
            {
                try
                {
                    oleDbTransaction.Rollback();
                }
                catch (Exception ex2)
                {
                    if (oleDbTransaction.Connection != null)
                    {
                        throw ex2;
                    }
                }
                throw ex;
            }
            finally
            {
                oleDbCommand.Dispose();
                CloseDB(oleDbConnection);
            }

            return blnFlag;
        }
    }
}
